﻿Imports System.Data.OleDb

Public Class ProfilesManagement
    Inherits ConnectionClass

    Public Sub InsertProfileNewUser(ByVal userID As Int32, ByVal profileAvatar As String)
        Try


            myConnection.Open()
            Dim SqlString As String = "INSERT INTO Profiles(UserID,ProfileName,ProfileSurname,ProfileHobbies,ProfileDob,ProfileAvatar) VALUES(@f1,@f2,@f3,@f4,@f5,@f6)"
            Dim newCmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            newCmd.CommandType = CommandType.Text
            newCmd.Parameters.AddWithValue("@f1", userID)
            newCmd.Parameters.AddWithValue("@f2", "Unknown")
            newCmd.Parameters.AddWithValue("@f3", " ")
            newCmd.Parameters.AddWithValue("@f4", " ")
            Dim dateGen As New DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day)
            newCmd.Parameters.AddWithValue("@f5", dateGen)
            newCmd.Parameters.AddWithValue("@f6", profileAvatar)
            newCmd.ExecuteNonQuery()
            myConnection.Close()
        Catch ex As Exception

        End Try
    End Sub

    Public Sub EditProfileByUserID(ByVal userID As Int32, ByVal name As String, ByVal surname As String, ByVal dob As DateTime, ByVal avatarUrl As String, ByVal hobbies As String)
        Try


            myConnection.Open()
            Dim SqlString As String = "UPDATE   Profiles Set ProfileName =@f1, ProfileSurname =@f2, ProfileHobbies =@f3, ProfileDob =@f4, ProfileAvatar =@f5 where UserID = @f6"
            Dim newCmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            newCmd.CommandType = CommandType.Text
            newCmd.Parameters.AddWithValue("@f1", name)
            newCmd.Parameters.AddWithValue("@f2", surname)
            newCmd.Parameters.AddWithValue("@f3", hobbies)
            newCmd.Parameters.AddWithValue("@f4", dob)
            newCmd.Parameters.AddWithValue("@f5", avatarUrl)
            newCmd.Parameters.AddWithValue("@f6", userID)
            newCmd.ExecuteNonQuery()
            myConnection.Close()
        Catch ex As Exception

        End Try
    End Sub

    Public Sub EditProfileByUserIDWithoutImage(ByVal userID As Int32, ByVal name As String, ByVal surname As String, ByVal dob As DateTime, ByVal hobbies As String)
        Try

            myConnection.Open()
            Dim SqlString As String = "UPDATE   Profiles Set ProfileName =@f1, ProfileSurname =@f2, ProfileHobbies =@f3, ProfileDob =@f4 where UserID = @f6"
            Dim newCmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            newCmd.CommandType = CommandType.Text
            newCmd.Parameters.AddWithValue("@f1", name)
            newCmd.Parameters.AddWithValue("@f2", surname)
            newCmd.Parameters.AddWithValue("@f3", hobbies)
            newCmd.Parameters.AddWithValue("@f4", dob)
            newCmd.Parameters.AddWithValue("@f6", userID)
            newCmd.ExecuteNonQuery()
            myConnection.Close()

        Catch ex As Exception

        End Try
    End Sub

    Public Function SelectProfileByUserID(ByVal userID As Int32) As OleDbDataReader
        Try

            myConnection.Open()
            Dim SqlString As String = "Select * From Profiles Where UserID=@f1"
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", userID)
            Dim dr As OleDbDataReader = cmd.ExecuteReader()
            Return dr
            myConnection.Close()

        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function SelectProfileByProfileID(ByVal profileID As Int32) As OleDbDataReader
        Try

            myConnection.Open()
            Dim SqlString As String = "Select * From Profiles Where ProfileID=@f1"
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", profileID)
            Dim dr As OleDbDataReader = cmd.ExecuteReader()
            Return dr
            myConnection.Close()

        Catch ex As Exception
            Return Nothing
        End Try
    End Function



    Public Function SelectSearchProfileByUserRole(ByVal searchContent As String, ByVal userRole As String) As OleDbDataReader
        Try


            myConnection.Open()
            Dim SqlString As String = "SELECT  Profiles.ProfileID, Profiles.UserID, Profiles.ProfileName, Profiles.ProfileSurname, Profiles.ProfileHobbies, Profiles.ProfileAvatar FROM ((aspnet_Roles INNER JOIN aspnet_UsersInRoles ON aspnet_Roles.RoleId = aspnet_UsersInRoles.RoleId) INNER JOIN Profiles ON aspnet_UsersInRoles.UserId = Profiles.UserID) WHERE        (Profiles.ProfileName LIKE '%' + @f1 + '%') AND (aspnet_Roles.RoleName = @f2) OR (Profiles.ProfileSurname LIKE '%' + '@f1' + '%') AND (aspnet_Roles.RoleName = @f2) OR (Profiles.ProfileHobbies LIKE '%' + '@f1' + '%') AND (aspnet_Roles.RoleName = @f2)"
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", searchContent)
            cmd.Parameters.AddWithValue("@f2", userRole)
            Dim dr As OleDbDataReader = cmd.ExecuteReader()
            Return dr
            myConnection.Close()
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function SelectSearchProfile(ByVal searchContent As String) As OleDbDataReader
        Try


            myConnection.Open()
            Dim SqlString As String = "SELECT Distinct Profiles.ProfileID, Profiles.UserID, Profiles.ProfileName, Profiles.ProfileSurname, Profiles.ProfileHobbies, Profiles.ProfileAvatar FROM ((aspnet_Roles INNER JOIN aspnet_UsersInRoles ON aspnet_Roles.RoleId = aspnet_UsersInRoles.RoleId) INNER JOIN Profiles ON aspnet_UsersInRoles.UserId = Profiles.UserID) WHERE        (Profiles.ProfileName LIKE '%' + @f1 + '%') OR (Profiles.ProfileSurname LIKE '%' + '@f1' + '%') OR (Profiles.ProfileHobbies LIKE '%' + '@f1' + '%')"
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", searchContent)
            Dim dr As OleDbDataReader = cmd.ExecuteReader()
            Return dr
            myConnection.Close()
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Sub EditProfileBlockByUserID(ByVal userID As Int32, ByVal block As Boolean)
        Try


            myConnection.Open()
            Dim SqlString As String = "UPDATE   Profiles Set ProfileBlocked=@f1 where UserID = @f2"
            Dim newCmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            newCmd.CommandType = CommandType.Text
            newCmd.Parameters.AddWithValue("@f1", block)
            newCmd.Parameters.AddWithValue("@f2", userID)
            newCmd.ExecuteNonQuery()
            myConnection.Close()
        Catch ex As Exception

        End Try
    End Sub

End Class
